Analysing a problem SQL

Before you begin to analyze a SQL, you might want to check if there are any quick fixes available. Also familiarise yourself with the many Data Storage problems where tuning will not help - this may save you some time.

If none of these appear to help, then further analysis is required. There are two main classes of problem:

• Low volume SQLs

  Low volume SQLs are ususally expected to read only a few rows, and to access tables via an index. Problems occur when indexes are missing, ignored, or misused.

• High volume SQLs

  High volume SQLs - ususally reports or batch jobs - can suffer when they use an index. Much has been written on this subject and it will not be repeated here. Suffice to say that when accessing a table or partition via an index, there is a point beyond which it becomes more efficient to simply read the entire table instead of using the index. The argument over exactly what proportion of the data that is (most estimates range from 4% to 50%) has become somewhat of a religious war. The truth is that there is no magic number, however if you have a SQL processing more than a few percent of a table or partition with more than 10,000 rows, you should try a Full Table Scan for comparison.

  For a detailed explanation of the causes behind this phenomenon, refer to the Performance Tuning book of the Oracle Online Documentation, or any good Oracle Performance Tuning manual.

---

In addition to the normal problems of high volume SQLs, there are two other special classes of High Volume problems worthy of a special note.

• High Volume DML: Is your SQL performing INSERTs, UPDATEs, or DELETEs?
• Procedural SQL: Are you using PL/SQL, Pro*C, or another procedural language that is looping through high volumes of data?

How did you go? If you still need better performance, think about the following:

• Are your expectations realistic? Look at the tables and work out how many blocks you actually need to read to process your query. Check TK*Prof to find out how many blocks it is actually processing. If they are not close, look back through this guide in case there's something you missed - you need to eliminate those redundant blocks.
  If they are close, show the TK*Prof output to the DBA and ask whether the blocks/sec disk access is reasonable - there may be a problem with the table, tablespace, or disk.
• Talk to your DBA about using the Parallel Query Server. It has been left out of this guide partly because when it is used too often it loses its effectiveness, but mostly because it promotes laziness. If parallel query returns a better result (and it usually will) people stop looking for a better solution.
• Take a look at the Advanced Objects page. Perhaps you can remodel the data to make access more efficient.
• Perhaps there is a greater problem at the database or system level. Review your tuning results with the DBA to see if they have any ideas.